package com.workManager.util;


import com.workManager.exception.BusinessException;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.springframework.web.multipart.MultipartFile;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 读取Excel辅助类
 */
public final class ExcelReadUtil {
	private ExcelReadUtil() {
	}

	private static final Log log = LogFactory.getLog(ExcelReadUtil.class);

	/**
	 * 获取Excel数据,返回List<String[]>
	 * 
	 * @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final List<String[]> excelToArrayList(String filePath, int... sheetNumber) throws Exception {
		List<String[]> resultList = null;
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			resultList = excelToArrayList(is, sheetNumber);
		} catch (Exception e) {
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return resultList;
	}

	/**
	 * 获取Excel数据,返回List<String[]>
	 * 
	 * @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final List<String[]> excelToArrayList(FileItem fileItem, int... sheetNumber) throws Exception {
		List<String[]> resultList = null;
		InputStream is = null;
		try {
			is = fileItem.getInputStream();
			resultList = excelToArrayList(is, sheetNumber);
		} catch (Exception e) {
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return resultList;
	}


	public static void validateExcel(String name ,MultipartFile file) throws Exception {
		List<String[]> fileData = null;//ExcelReadUtil.excelToList(file.getInputStream());
		Set<String> fileDataSet = new HashSet<String>();
		if(name.contains("直接费用")||name.contains("人天数据")||name.contains("项目总成本")){
           for (int i =0;i<fileData.size();i++){
			   String[] s = fileData.get(i);
			   fileDataSet.add(s[0]+s[1]+s[2]);
		   }
		}
        if( name.contains("项目人工")){
			for (int i =0;i<fileData.size();i++){
				String[] s = fileData.get(i);
				fileDataSet.add(s[0]+s[1]+s[2]+s[3]);
			}
		}

		if(fileDataSet!=null && fileData !=null && fileData.size()!=fileDataSet.size()){

			throw  new BusinessException("Excel文件存在重复数据,请重新录入！");
		}




	}


	public  static final  List<String[]> excelToList(String name,InputStream is) throws  Exception{
		ArrayList<String[]> resultList = new ArrayList<String[]>();
		Workbook wb = null;

		wb = WorkbookFactory.create(is);
		Sheet sheet = wb.getSheetAt(0);

		int rows1 = sheet.getPhysicalNumberOfRows();
		int column = sheet.getRow(0).getPhysicalNumberOfCells();

		for(int i = 0;i<rows1;i++){
			String[] object = new String[column];
			for(int j=0;j<column;j++) {
				if (null != sheet.getRow(i).getCell(j)) {
					Cell cell =sheet.getRow(i).getCell(j);

					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						if (DateUtil.isCellDateFormatted(cell)) {
							SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
							String date = format.format(cell.getDateCellValue());
							object[j] = date;

						} else {
							// 返回数值类型的值
							Object inputValue = null;// 单元格值
							Long longVal = Math.round(cell.getNumericCellValue());
							Double doubleVal = cell.getNumericCellValue();
							if(Double.parseDouble(longVal + ".0") == doubleVal){   //判断是否含有小数位.0
								inputValue = longVal;
							}
							else{
								inputValue = doubleVal;
							}
							DecimalFormat df = new DecimalFormat("#.##");    //格式化为两位小数，按自己需求选择；

							object[j] = String.valueOf(df.format(inputValue));
						}
					} else {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						//System.out.println("i的值是:"+i+"j的值是："+j+"type是："+sheet.getRow(i).getCell(j).getCellType());
						object[j] = cell.getStringCellValue();

					}

				}else{
					object[j] = "";
				}
			}
			resultList.add(object);

		}




       //检查有没有重复数据
		Set<String> fileDataSet = new HashSet<String>();
		if(name.contains("直接费用")||name.contains("人天数据")||name.contains("项目总成本")){
			for (int i =0;i<resultList.size();i++){
				String[] s = resultList.get(i);
				fileDataSet.add(s[0]+s[1]+s[2]);//前三列
			}
			if(fileDataSet!=null && resultList !=null && resultList.size()!=fileDataSet.size()){


				Iterator t = fileDataSet.iterator();

				while (t.hasNext()){
					String s = t.next().toString();
					int count = 0;
					for (int j = 0;j<resultList.size();j++){
						String[] r1 = resultList.get(j);
						if(s.equals(r1[0]+r1[1]+r1[2])){
							count +=1;
							if(count>1){
								int temp = j+1;
								throw  new BusinessException("Excel文件第"+temp+"行存在重复数据,请重新录入！");
							}

						}
					}

				}

				throw  new BusinessException("Excel文件存在重复数据,请重新录入！");
			}

		}
		if( name.contains("项目人工")){
			for (int i =0;i<resultList.size();i++){
				String[] s = resultList.get(i);
				fileDataSet.add(s[0]+s[1]+s[2]+s[3]);
			}

			if(fileDataSet!=null && resultList !=null && resultList.size()!=fileDataSet.size()){
				Iterator t = fileDataSet.iterator();

				while (t.hasNext()){
					String s = t.next().toString();
					int count = 0;
					for (int j = 0;j<resultList.size();j++){
						String[] r1 = resultList.get(j);
						if(s.equals(r1[0]+r1[1]+r1[2]+r1[3])){
							count +=1;
							if(count>1){
								int temp = j+1;
								throw  new BusinessException("Excel文件第"+temp+"行存在重复数据,请重新录入！");
							}

						}
					}

				}
				throw  new BusinessException("Excel文件存在重复数据,请重新录入！");
			}
		}


		return  resultList;
	}




	/**
	 * 获取Excel数据,返回List<String[]>;
	 * 
	 * @param //sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final List<String[]> excelToArrayList(InputStream is, int... sheetNumber) throws Exception {
		ArrayList<String[]> resultList = new ArrayList<String[]>();
		Workbook wb = null;
		try {
			wb = WorkbookFactory.create(is);
			if (sheetNumber == null || sheetNumber.length < 1) {
				int sheetCount = wb.getNumberOfSheets();// 得到所有Excel中页的列表.
				sheetNumber = new int[sheetCount];
				for (int i = 0; i < sheetNumber.length; i++) {
					sheetNumber[i] = i + 1;
				}
			}
			Sheet sheet = null;
			for (int k = 0; k < sheetNumber.length; k++) {// 循环工作表
				sheet = wb.getSheetAt(sheetNumber[k] - 1);
				int rsRows = sheet.getRow(0) == null ? -1 : sheet.getLastRowNum();
				log.warn("Sheet " + sheetNumber[k] + "." + wb.getSheetName(sheetNumber[k] - 1) + ":" + (rsRows + 1));
				//假设第一行是表头
				int headCount = sheet.getRow(0).getLastCellNum();
				for (int i = 0; i <= rsRows; i++) {// 循环行
					Row row = sheet.getRow(i);
					if (row != null) {
						int cellCount = row.getLastCellNum();
						if (cellCount > 0) {
							/*String[] objects = new String[cellCount];
							for (int j = 0; j < cellCount; j++) {// 读取单元格
								objects[j] = getCellValue(row.getCell(j));
							}
							resultList.add(objects);*/
							String[] objects = new String[headCount];
							for (int j = 0; j < headCount; j++) {// 读取单元格
								objects[j] = getCellValue(row.getCell(j));
							}
							resultList.add(objects);
						}
					}
				}
			}
		} catch (Exception e) {
			log.error("Read excel error.", e);
			throw e;
		}
		return resultList;
	}

	/**
	 * 读取Excel中的数据.将这些数据放入到一个三维数组中.
	 * 
	 * @param filePath 文件路径.
	 * @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 * @deprecated:读取Excel中的数据将它放入到ArrayList数组中(此为三维数组).
	 */
	public static final ArrayList<ArrayList<Object>> readExcel(String filePath, int... sheetNumber) throws Exception {
		ArrayList<ArrayList<Object>> subdata = null;
		InputStream is = null;
		try {
			is = new FileInputStream(filePath);
			subdata = readExcel(is, sheetNumber);
		} catch (Exception e) {
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return subdata;
	}

	/**
	 * 读取Excel中的数据.将这些数据放入到一个三维数组中.
	 * 
	 * @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final ArrayList<ArrayList<Object>> readExcel(FileItem fileItem, int... sheetNumber) throws Exception {
		ArrayList<ArrayList<Object>> subdata = null;
		InputStream is = null;
		try {
			is = fileItem.getInputStream();
			subdata = readExcel(is, sheetNumber);
		} catch (Exception e) {
			throw e;
		} finally {
			if (is != null) {
				is.close();
			}
		}
		return subdata;
	}

	/**
	 * 读取Excel中的数据.将这些数据放入到一个三维数组中.
	 * 
	 * @param sheetNumber 读取工作表的下标(从1开始).可有可无,默认读取所有表单.
	 */
	public static final ArrayList<ArrayList<Object>> readExcel(InputStream is, int... sheetNumber) throws Exception {
		ArrayList<ArrayList<Object>> subdata = new ArrayList<ArrayList<Object>>();
		Workbook wb = null;
		wb = WorkbookFactory.create(is);
		if (sheetNumber == null || sheetNumber.length < 1) {
			int sheetCount = wb.getNumberOfSheets();// 得到所有Excel中页的列表.
			sheetNumber = new int[sheetCount];
			for (int i = 0; i < sheetNumber.length; i++) {
				sheetNumber[i] = i + 1;
			}
		}
		Sheet sheet = null;
		ArrayList<ArrayList<String>> alList = null;
		ArrayList<String> tablenames = null;
		ArrayList<Object> tableAndContents = null;
		for (int a = 0; a < sheetNumber.length; a++) {
			alList = new ArrayList<ArrayList<String>>();
			tablenames = new ArrayList<String>();
			tableAndContents = new ArrayList<Object>();
			String tablename = wb.getSheetName(sheetNumber[a] - 1).trim();
			int b = 0;
			sheet = wb.getSheetAt(sheetNumber[a] - 1);
			int rsRows = sheet.getRow(0) == null ? -1 : sheet.getLastRowNum();
			log.warn("Sheet " + sheetNumber[a] + "." + tablename + ":" + (rsRows + 1));
			for (int i = 1; i <= rsRows; i++) {
				ArrayList<String> al = new ArrayList<String>();
				Row row = sheet.getRow(i);
				int cellCount = row.getLastCellNum();
				for (int j = 0; j < cellCount; j++) {
					// 通用的获取cell值的方式,返回字符串
					String strc00 = getCellValue(row.getCell(j));
					// 获得cell具体类型值的方式得到内容.
					al.add(j, strc00);
				}
				alList.add(b, al);
				b++;
			}
			tablenames.add(tablename);
			tableAndContents.add(0, tablenames);
			tableAndContents.add(1, alList);
			subdata.add(a, tableAndContents);
		}
		return subdata;
	}

	/** 根据Cell类型设置数据 */
	private static String getCellValue(Cell cell) {
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_BLANK:
				cellvalue = "";
				break;
			case Cell.CELL_TYPE_BOOLEAN:
				cellvalue = Boolean.toString(cell.getBooleanCellValue());
				break;
			// 数值
			case Cell.CELL_TYPE_NUMERIC:
				if ( DateUtil.isCellDateFormatted(cell)) {
					cellvalue = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue());
				} else {
					cell.setCellType(Cell.CELL_TYPE_STRING);
					String temp = cell.getStringCellValue();
					// 判断是否包含小数点，如果不含小数点，则以字符串读取，如果含小数点，则转换为Double类型的字符串
					if (temp.indexOf(".") > -1) {
						cellvalue = String.valueOf(new Double(temp)).trim();
					} else {
						cellvalue = temp.trim();
					}
				}
				break;
			case Cell.CELL_TYPE_STRING:
				cellvalue = cell.getStringCellValue().trim();
				break;
			case Cell.CELL_TYPE_ERROR:
				cellvalue = "";
				break;
			case Cell.CELL_TYPE_FORMULA:
				cell.setCellType(Cell.CELL_TYPE_STRING);
				cellvalue = cell.getStringCellValue();
				if (cellvalue != null) {
					cellvalue = cellvalue.replaceAll("#N/A", "").trim();
				}
				break;
			default:
				cellvalue = "";
				break;
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	/**
	 * 判断是否空行
	 * @param rowData
	 * @return
	 */
	public static boolean isBlankRow(String[] rowData){
		boolean isBlank = true;
		if(rowData != null) {
			for (int i = 0; i < rowData.length; i++) {
				if (null!=rowData[i]&&!"".equals(rowData[i].trim())) {
					isBlank = false;
					break;
				}
			}
		}
		return isBlank;
	}

}
